package fact.view.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.annotation.Resource;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;
import javax.sql.DataSource;

import fact.view.DTO.MakeDTO;
import fact.view.DTO.MarketDTO;


@ManagedBean(name = "marketModelBean")
@RequestScoped
public class MarketModelBean {
	
	@Resource(name="jdbc/factentry")
	private DataSource ds;
	
	public ArrayList<MarketDTO> getMarketList() throws SQLException
	{
		ArrayList<MarketDTO> marketList=new ArrayList<MarketDTO>();
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Select * from MST_MARKETS Order by Market_Name");  
		ResultSet result =  ps.executeQuery();
		MarketDTO m=null;
				while(result.next())
				{
					m=new MarketDTO();
					m.setMarketId(result.getInt("MARKET_ID"));
					m.setMarketName(result.getString("MARKET_NAME"));
					
					marketList.add(m);
				}
		
		
		return marketList;
	}
	
	public MarketDTO getMarket(String marketName) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Select * from MST_MARKETS Where upper(MARKET_NAME)=upper(?)");  
		ps.setString(1, marketName);
		ResultSet result =  ps.executeQuery();
		MarketDTO m=null;
				if(result.next())
				{
					m=new MarketDTO();
					m.setMarketId(result.getInt("MARKET_ID"));
					m.setMarketName(result.getString("MARKET_NAME"));
					
				}
		
		
		return m;
	}
	
	public MarketDTO getMarket(int marketId) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Select * from MST_MARKETS Where MARKET_ID=?");  
		ps.setInt(1, marketId);
		ResultSet result =  ps.executeQuery();
		MarketDTO m=null;
				if(result.next())
				{
					m=new MarketDTO();
					m.setMarketId(result.getInt("MARKET_ID"));
					m.setMarketName(result.getString("MARKET_NAME"));
					
				}
		
		
		return m;
	}
	
	public boolean addMarket(String marketName) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Insert into MST_MARKETS Values(SQN_MARKET.NEXTVAL,?)");  
		ps.setString(1, marketName);
		int result =  ps.executeUpdate();
		
		if(result==1)
			return true;
		else
			return false;
		
	}
	
	public boolean deleteMarket(int marketId) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Delete MST_MARKETS Where MARKET_ID=?");  
		ps.setInt(1, marketId);
		int result =  ps.executeUpdate();
		
		if(result==1)
			return true;
		else
			return false;
		
	}
	
	public boolean editMarket(String marketName,int marketId) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Update MST_MARKETS Set MARKET_NAME=? Where MARKET_ID=?");  
		ps.setString(1, marketName);
		ps.setInt(2, marketId);
		int result =  ps.executeUpdate();
		
		if(result==1)
			return true;
		else
			return false;
		
	}
	
	public String[] getMarketList(String userId) throws SQLException
	{
		ArrayList<MarketDTO> marketList=new ArrayList<MarketDTO>();
		String[] marketArrList;
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Select * from RESPONSIBILITIES Where user_id=?");  
		ps.setString(1, userId);
		ResultSet result =  ps.executeQuery();
		MarketDTO m=null;
				while(result.next())
				{
					m=new MarketDTO();
					m.setMarketId(result.getInt("MARKET_ID"));
					marketList.add(m);
				}
				
				marketArrList=new String[marketList.size()];
		
		for(int i=0;i<marketList.size();i++)
		{
			marketArrList[i]=String.valueOf(marketList.get(i).getMarketId());
		}
		
		return marketArrList;
	}

}
